﻿<%@ WebHandler Language="C#" Class="ContainerStepRecord" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 批次派报检信息查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:王明宇
 * Date:
 **/

public class ContainerStepRecord : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getQuerySql(JObject jObject)
    {
        string factoryid = "", workcenterid = "", teamid = "";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            teamid = selectOrg[2].ToString();
        }
        else if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }
        string sql = @"
SELECT f.factoryname,'报工' datasource,cci.mfgordername,cci.familylistnames,cci.productname || ':' || cci.productrevision productinfo,p.charactertype,
       cci.productdescription,cci.containername,ctr.workcentername,ctr.workflowstepname,
       CASE WHEN wfs.controlcode='LY05' THEN '是' ELSE '否' END isShipSpec,
       CASE WHEN wfs.controlcode='LY07' THEN '是' ELSE '否' END isVirtuallySpec,
       CASE WHEN s.factoryid!=ctr.factoryid AND s.factoryid!='' THEN '是' ELSE '否' END isTransferSpec,
       CASE WHEN s.lastCheck=1 THEN '是' ELSE '否' END isCheckSpec,
       CASE WHEN s.isspotcheck=1 THEN '是' ELSE '否' END isSpecialSpec,
       CASE WHEN s.iskey=1 THEN '是'
            WHEN s.iskey=0 THEN '否'
            else ''
       END iskey,
       ctr.workflowname || ':' || ctr.workflowrevision workflowinfo,
       ctr.dispatchemployeename,ctr.dispatchdate,
       e2.fullname receiver,cdt.recvdate receivdate,
       CASE WHEN cdt.recvdate IS NOT NULL AND ctr.reportdate IS NOT NULL THEN ROUND((ctr.reportdate-cdt.recvdate) * 24 * 60,2)
            ELSE NULL 
       END reportwaitdays,
       ctr.dispatchqty,ctr.teamname,ctr.dispatchresourcename,ctr.reportemployeename reportemployeename,ctr.reportdate,ctr.reportqty,ctr.checkemployeename,ctr.checkdate,
       nvl(ctr.lossqty,0) lossqty,lrs.lossreasonname,(ctr.reportqty - nvl(ctr.lossqty,0)) hegeQty,
       cdt.plannedstartdate,cdt.plannedfinishdate,ck.fullname tocheck,
       CASE WHEN ctr.checkdate IS NOT NULL THEN '已完工'
            WHEN ctr.checkdate IS NULL AND ctr.reportdate IS NOT NULL THEN '待检验'
            WHEN ctr.reportdate IS NULL THEN '待报工'
       END status,u.notes
FROM containercurrentinfo cci
LEFT JOIN product p on p.productid=cci.productid
INNER JOIN containersteprecord ctr ON ctr.containerid = cci.containerid
LEFT JOIN spec s on s.specid=ctr.specid
LEFT JOIN workflowstep wfs on wfs.workflowstepid=ctr.workflowstepid
LEFT JOIN spectype st on s.spectypeid=st.spectypeid
LEFT JOIN factory f on f.factoryid=cci.factoryid
LEFT JOIN containerstepdispatch cdt ON cdt.containerstepdispatchid = ctr.dispatchid AND cdt.isuse = 0
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = ctr.containersteprecordid
LEFT JOIN CheckQtyChangeRecord cr on cr.containerstepworkreportid=csw.containerstepworkreportid AND cr.isuse = 0
LEFT JOIN lossreason lrs ON lrs.lossreasonid = cr.reasonid
LEFT JOIN employee ck ON ck.employeeid = csw.checkerid
LEFT JOIN 
(
  SELECT u.containerid,u.workflowstepid,listagg(u.notes,'、') WITHIN GROUP (ORDER BY u.notes) notes
  FROM containerstepattribute u
  LEFT JOIN containercurrentinfo cci ON cci.containerid = u.containerid
  WHERE u.notes IS NOT NULL ";
        // 分厂
        if (factoryid != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        // 机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        sql += @"
  GROUP BY u.containerid,u.workflowstepid
) u ON u.containerid = ctr.containerid AND u.workflowstepid = ctr.workflowstepid
LEFT JOIN employee e2 ON e2.employeeid = cdt.recvemployeeid
WHERE cci.containerstatus = 1";
        // 分厂
        if (factoryid != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        // 工区
        if (workcenterid != "")
        {
            sql += string.Format(" AND ctr.workcenterid = '{0}'", workcenterid);
        }

        // 班组
        if (teamid != "")
        {
            sql += string.Format(" AND ctr.teamid = '{0}'", teamid);
        }

        // 订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername Like '%{0}%'", jObject["mfgordername"].ToString());
        }

        // 机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
        }

        // 工序号
        if (jObject["specno"] != null && jObject["specno"].ToString() != "")
        {
            sql += string.Format(" AND ctr.specno like '%{0}%'", jObject["specno"].ToString());
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 派工人
        if (jObject["dispatcher"] != null && jObject["dispatcher"].ToString() != "")
        {
            sql += string.Format(" AND ctr.dispatchemployeeid = '{0}'", jObject["dispatcher"].ToString());
        }

        // 领工人
        if (jObject["reveiver"] != null && jObject["reveiver"].ToString() != "")
        {
            sql += string.Format(" AND cdt.recvemployeeid = '{0}'", jObject["reveiver"].ToString());
        }

        // 报工人
        if (jObject["worker"] != null && jObject["worker"].ToString() != "")
        {
            sql += string.Format(" AND ctr.reportemployeeid = '{0}'", jObject["worker"].ToString());
        }

        // 检验人
        if (jObject["checker"] != null && jObject["checker"].ToString() != "")
        {
            sql += string.Format(" AND ctr.checkemployeeid = '{0}'", jObject["checker"].ToString());
        }

        // 派工日期
        if (jObject["dispatchdate"] != null && jObject["dispatchdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["dispatchdate"].ToString());
            sql += string.Format(" AND ctr.dispatchdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND ctr.dispatchdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
        }

        // 领工时间
        if (jObject["reveivedate"] != null && jObject["reveivedate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["reveivedate"].ToString());
            sql += string.Format(" AND cdt.recvdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND cdt.recvdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
        }

        // 报工日期
        if (jObject["reportdate"] != null && jObject["reportdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["reportdate"].ToString());
            sql += string.Format(" AND ctr.reportdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND ctr.reportdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
        }

        // 检验日期
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND ctr.checkdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss') ", arr[0].ToString());
            sql += string.Format(" AND ctr.checkdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss') ", arr[1].ToString());
        }

        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "待报工")
            {
                sql += " AND ctr.reportdate IS NULL ";
            }
            if (jObject["status"].ToString() == "待检验")
            {
                sql += " AND ctr.reportdate IS NOT NULL AND ctr.checkdate IS NULL ";
            }
            if (jObject["status"].ToString() == "已完工")
            {
                sql += " AND ctr.checkdate IS NOT NULL ";
            }
        }

        if (jObject["spectypename"] != null && jObject["spectypename"].ToString() != "")
        {
            sql += string.Format(" AND st.spectypename like '%{0}%'", jObject["spectypename"].ToString());
        }

        if (jObject["iskey"] != null && jObject["iskey"].ToString() != "")
        {
            sql += string.Format(" AND s.iskey = '{0}'", jObject["iskey"].ToString());
        }

        if (jObject["charactertype"] != null && jObject["charactertype"].ToString() != "")
        {
            sql += string.Format(" AND p.charactertype = '{0}'", jObject["charactertype"].ToString());
        }

        // 是否固定外委
        if (jObject["isShipSpec"] != null && jObject["isShipSpec"].ToString() == "1")
        {
            sql += " and wfs.controlcode='LY05' ";
        }

        // 是否固定转工
        if (jObject["isTransferSpec"] != null && jObject["isTransferSpec"].ToString() == "1")
        {
            sql += " and s.factoryid!=ctr.factoryid AND s.factoryid!='' ";
        }

        // 是否虚拟工序
        if (jObject["isVirtuallySpec"] != null && jObject["isVirtuallySpec"].ToString() == "1")
        {
            sql += " and wfs.controlcode='LY07' ";
        }

        // 是否特殊工序
        if (jObject["isSpecialSpec"] != null && jObject["isSpecialSpec"].ToString() == "1")
        {
            sql += " and s.isspotcheck=1 ";
        }

        // 是否检序
        if (jObject["isCheckSpec"] != null && jObject["isCheckSpec"].ToString() == "1")
        {
            sql += " and s.lastCheck=1 ";
        }

        if (jObject["shownotes"] != null && jObject["shownotes"].ToString() == "是")
        {
            sql += @"
UNION ALL

SELECT f.factoryname,'工序备注维护' datasource,cci.mfgordername,cci.familylistnames,cci.productname || ':' || cci.productrevision productinfo,p.charactertype,
       cci.productdescription,cci.containername,NULL workcentername,ws.workflowstepname,
       CASE WHEN ws.controlcode='LY05' THEN '是' ELSE '否' END isShipSpec,
       CASE WHEN ws.controlcode='LY07' THEN '是' ELSE '否' END isVirtuallySpec,
       CASE WHEN s.factoryid!=cci.originalfactoryid AND s.factoryid!='' THEN '是' ELSE '否' END isTransferSpec,
       CASE WHEN s.lastCheck=1 THEN '是' ELSE '否' END isCheckSpec,
       CASE WHEN s.isspotcheck=1 THEN '是' ELSE '否' END isSpecialSpec,
       CASE WHEN s.iskey=1 THEN '是'
            WHEN s.iskey=0 THEN '否'
            else ''
       END iskey,
       wfb.workflowname || ':' || wf.workflowrevision workflowinfo,
       NULL dispatchemployeename,NULL dispatchdate,
       NULL receiver,NULL receivdate,
       NULL reportwaitdays,
       NULL dispatchqty,NULL teamname,NULL dispatchresourcename,NULL reportemployeename,NULL reportdate,NULL reportqty,NULL checkemployeename,NULL checkdate,
       NULL lossqty,lrs.lossreasonname,NULL hegeQty,
       NULL plannedstartdate,NULL plannedfinishdate,NULL tocheck,
       NULL status,t.notes
FROM 
(
  SELECT u.containerid,csd.containersteprecordid,u.workflowstepid,listagg(u.notes,'、') WITHIN GROUP (ORDER BY u.notes) notes
  FROM containerstepattribute u
  LEFT JOIN containercurrentinfo cci ON cci.containerid = u.containerid
  LEFT JOIN containersteprecord csd ON csd.containerid = u.containerid AND csd.workflowstepid = u.workflowstepid
  WHERE u.notes IS NOT NULL AND csd.containersteprecordid IS NULL ";

            // 分厂
            if (factoryid != "")
            {
                sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
            }

            // 机型
            if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
            {
                sql += string.Format(" AND cci.familylistnames = '{0}'", jObject["productfamilyname"].ToString());
            }

            // 零件图号
            if (jObject["productname"] != null && jObject["productname"].ToString() != "")
            {
                sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
            }

            // 流水卡号
            if (jObject["containername"] != null && jObject["containername"].ToString() != "")
            {
                sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
            }

            sql += @"
  GROUP BY u.containerid,csd.containersteprecordid,u.workflowstepid
) t
LEFT JOIN containercurrentinfo cci ON cci.containerid = t.containerid
left join product p on p.productid=cci.productid
left join factory f on f.factoryid=cci.factoryid
LEFT JOIN containersteprecord ctr ON ctr.containersteprecordid = t.containersteprecordid
left join spec s on s.specid=ctr.specid
left join spectype st on s.spectypeid=st.spectypeid
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = ctr.containersteprecordid
left join CheckQtyChangeRecord cr on cr.containerstepworkreportid=csw.containerstepworkreportid
LEFT JOIN lossreason lrs ON lrs.lossreasonid = cr.reasonid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = t.workflowstepid
LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
WHERE 1=1";
            if (jObject["iskey"] != null && jObject["iskey"].ToString() != "")
            {
                sql += string.Format(" AND s.iskey = '{0}'", jObject["iskey"].ToString());
            }

            if (jObject["charactertype"] != null && jObject["charactertype"].ToString() != "")
            {
                sql += string.Format(" AND p.charactertype = '{0}'", jObject["charactertype"].ToString());
            }

            // 是否固定外委
            if (jObject["isShipSpec"] != null && jObject["isShipSpec"].ToString() == "1")
            {
                sql += " and ws.controlcode='LY05' ";
            }

            // 是否固定转工
            if (jObject["isTransferSpec"] != null && jObject["isTransferSpec"].ToString() == "1")
            {
                sql += " and s.factoryid!=cci.originalfactoryid AND s.factoryid!='' ";
            }

            // 是否虚拟工序
            if (jObject["isVirtuallySpec"] != null && jObject["isVirtuallySpec"].ToString() == "1")
            {
                sql += " and ws.controlcode='LY07' ";
            }

            // 是否特殊工序
            if (jObject["isSpecialSpec"] != null && jObject["isSpecialSpec"].ToString() == "1")
            {
                sql += " and s.isspotcheck=1 ";
            }

            // 是否检序
            if (jObject["isCheckSpec"] != null && jObject["isCheckSpec"].ToString() == "1")
            {
                sql += " and s.lastCheck=1 ";
            }
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = getQuerySql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getQuerySql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        dt.Columns["mfgordername"].Caption = "订单号";
        dt.Columns["familylistnames"].Caption = "机型";
        dt.Columns["productinfo"].Caption = "零件号及版本";
        dt.Columns["charactertype"].Caption = "特性类型";
        dt.Columns["workflowinfo"].Caption = "工艺及版本";
        dt.Columns["notes"].Caption = "工艺备注";
        dt.Columns["datasource"].Caption = "数据来源";
        dt.Columns["productdescription"].Caption = "零件名称";
        dt.Columns["containername"].Caption = "流水卡号";
        dt.Columns["workcentername"].Caption = "主制工区";
        dt.Columns["workflowstepname"].Caption = "工序";
        dt.Columns["iskey"].Caption = "关键序";
        dt.Columns["isShipSpec"].Caption = "固定外委";
        dt.Columns["isVirtuallySpec"].Caption = "虚拟工序";
        dt.Columns["isTransferSpec"].Caption = "固定转工";
        dt.Columns["isCheckSpec"].Caption = "检序";
        dt.Columns["isSpecialSpec"].Caption = "特殊工序";
        dt.Columns["dispatchemployeename"].Caption = "派工人";
        dt.Columns["dispatchdate"].Caption = "派工时间";
        dt.Columns["dispatchqty"].Caption = "派工数量";
        dt.Columns["teamname"].Caption = "班组";
        dt.Columns["dispatchresourcename"].Caption = "设备";
        dt.Columns["receiver"].Caption = "领工人";
        dt.Columns["receivdate"].Caption = "领工时间";
        dt.Columns["reportwaitdays"].Caption = "领-报间隔分钟数";
        dt.Columns["reportemployeename"].Caption = "报工人";
        dt.Columns["factoryname"].Caption = "分厂";
        dt.Columns["reportdate"].Caption = "报工时间";
        dt.Columns["reportqty"].Caption = "报工数";
        dt.Columns["checkemployeename"].Caption = "检验员";
        dt.Columns["checkdate"].Caption = "检验日期";
        dt.Columns["lossqty"].Caption = "报废数";
        dt.Columns["lossreasonname"].Caption = "报废原因";
        dt.Columns["hegeQty"].Caption = "合格数";
        dt.Columns["plannedstartdate"].Caption = "派工开始日期";
        dt.Columns["plannedfinishdate"].Caption = "要求完成日期";
        dt.Columns["status"].Caption = "状态";
        dt.Columns["tocheck"].Caption = "待检人";
        ExportHelper.ExportExcel(dt, "批次派报检信息查询报表");
    }
}